﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace DBProvider.Providers
{
	public class SurveyProvider : ISurveyProvider
	{
		#region ISurveyProvider Members

		public Survey CreateSurvey(Survey theNewSurvey, out string msg)
		{
			if (theNewSurvey == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			Survey result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.Surveys.Count(s => s.SurveyTitle == theNewSurvey.SurveyTitle) > 0;

					if (isExist)
					{
						msg = "标题已存在";
					}
					else
					{
						db.Surveys.InsertOnSubmit(theNewSurvey);
						db.SubmitChanges();

						result = db.Surveys.SingleOrDefault(s => s.SurveyTitle == theNewSurvey.SurveyTitle);
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteSurvey(int surveyID)
		{
			if (surveyID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbSurveyQuestionItem = from sqi in db.SurveyQuestionItems
														where (from sq in db.SurveyQuestions
																 where sq.SurveyID == surveyID
																 select sq.SurveyQuestionID).Contains(sqi.SurveyQuestionID)
														select sqi;

					var dbSurveyAnswer = from sa in db.SurveyAnswers
												where (from sq in db.SurveyQuestions
														 where sq.SurveyID == surveyID
														 select sq.SurveyQuestionID).Contains(sa.SurveyQuestionID)
												select sa;

					var dbSurveyQuestion = from sq in db.SurveyQuestions
												  where sq.SurveyID == surveyID
												  select sq;

					var dbRLT_SurveyAndShareTo = from ss in db.RLT_SurveyAndShareTos
																			 where ss.SurveyID == surveyID
																			 select ss;

					var submit = false;

					if (dbSurveyQuestionItem != null)
					{
						db.SurveyQuestionItems.DeleteAllOnSubmit(dbSurveyQuestionItem);
						submit = true;
					}

					if (dbSurveyAnswer != null)
					{
						submit = true;
						db.SurveyAnswers.DeleteAllOnSubmit(dbSurveyAnswer);
					}

					if (dbSurveyQuestion != null)
					{
						db.SurveyQuestions.DeleteAllOnSubmit(dbSurveyQuestion);
						submit = true;
					}

					if(dbRLT_SurveyAndShareTo != null)
					{
						db.RLT_SurveyAndShareTos.DeleteAllOnSubmit(dbRLT_SurveyAndShareTo);
						submit = true;
					}

					var dbSurvey = db.Surveys.SingleOrDefault(s => s.SurveyID == surveyID);

					if (dbSurvey != null)
					{
						db.Surveys.DeleteOnSubmit(dbSurvey);
						submit = true;
					}

					if (submit)
					{
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateSurvey(Survey theSurvey)
		{
			if (theSurvey == null || theSurvey.SurveyID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbSurvey = db.Surveys.SingleOrDefault(s => s.SurveyID == theSurvey.SurveyID);

					if (dbSurvey != null)
					{
						var isExist = db.Surveys.Count(s => s.SurveyTitle == theSurvey.SurveyTitle) > 0;

						if (!isExist)
						{
							dbSurvey.SurveyDate = theSurvey.SurveyDate;
							dbSurvey.SurveyDescription = theSurvey.SurveyDescription;
							dbSurvey.SurveyTitle = theSurvey.SurveyTitle;

							db.SubmitChanges();

							result = true;
						}
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public Survey GetSurvey(int theSurveyID)
		{
			if (theSurveyID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			Survey result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					result = db.Surveys.SingleOrDefault(s => s.SurveyID == theSurveyID);
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Survey> GetAllSurvey(int drugStoreID)
		{
			if (drugStoreID <= 0)
			{
				throw new ArgumentException();
			}

			List<Survey> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					result = db.Surveys.Where(s => s.DrugStoreID == drugStoreID).OrderByDescending(s => s.SurveyDate).ToList();
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Survey> GetAllSurvey(int drugStoreID, int pageIndex, int pageSize, out int totalRecords)
		{
			if (drugStoreID <= 0)
			{
				throw new ArgumentException();
			}

			List<Survey> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					totalRecords = db.Surveys.Where(s => s.DrugStoreID == drugStoreID).Count();

					var data = db.Surveys.Where(s => s.DrugStoreID == drugStoreID).OrderByDescending(s => s.SurveyDate).Skip(pageIndex * pageSize).Take(pageSize);
					if (data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public SurveyQuestion CreateSurveyQuestion(SurveyQuestion theNewSurveyQuestion, out string msg)
		{
			if (theNewSurveyQuestion == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			SurveyQuestion result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.SurveyQuestions.Count(sq => sq.SurveyID == theNewSurveyQuestion.SurveyID && sq.SurveyQuestionContent == theNewSurveyQuestion.SurveyQuestionContent) > 0;

					if (isExist)
					{
						msg = "问题已存在";
					}
					else
					{
						db.SurveyQuestions.InsertOnSubmit(theNewSurveyQuestion);
						db.SubmitChanges();

						result = db.SurveyQuestions.SingleOrDefault(sq => sq.SurveyQuestionContent == theNewSurveyQuestion.SurveyQuestionContent && sq.SurveyID == theNewSurveyQuestion.SurveyID);
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteSurveyQuestion(int theSurveyQuestionID)
		{
			if (theSurveyQuestionID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbSurveyQuestion = db.SurveyQuestions.SingleOrDefault(sq => sq.SurveyQuestionID == theSurveyQuestionID);

					if (dbSurveyQuestion != null)
					{
						var surveyQuestionAnswers = db.SurveyAnswers.Where(sa => sa.SurveyQuestionID == theSurveyQuestionID);
						db.SurveyAnswers.DeleteAllOnSubmit(surveyQuestionAnswers);

						var surveyQuestionItems = db.SurveyQuestionItems.Where(sqi => sqi.SurveyQuestionID == theSurveyQuestionID);
						db.SurveyQuestionItems.DeleteAllOnSubmit(surveyQuestionItems);

						db.SurveyQuestions.DeleteOnSubmit(dbSurveyQuestion);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateSurveyQuestion(SurveyQuestion theSurveyQuestion)
		{
			if (theSurveyQuestion == null || theSurveyQuestion.SurveyQuestionID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbSurveyQuestion = db.SurveyQuestions.SingleOrDefault(sq => sq.SurveyQuestionID == theSurveyQuestion.SurveyQuestionID);

					if (dbSurveyQuestion != null)
					{
						dbSurveyQuestion.SurveyID = theSurveyQuestion.SurveyID;
						dbSurveyQuestion.SurveyQuestionContent = theSurveyQuestion.SurveyQuestionContent;
						dbSurveyQuestion.SurveyQuestionType = theSurveyQuestion.SurveyQuestionType;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<SurveyQuestion> GetSurveyQuestionBySurvey(int theSurveyID)
		{
			if (theSurveyID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<SurveyQuestion> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = db.SurveyQuestions.Where(sq => sq.SurveyID == theSurveyID);

					if (data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public SurveyQuestionItem CreateSurveyQuestionItem(SurveyQuestionItem theNewSurveyQuestionItem, out string msg)
		{
			if (theNewSurveyQuestionItem == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			SurveyQuestionItem result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.SurveyQuestionItems.Count(sqi => sqi.SurveyQuestionID == theNewSurveyQuestionItem.SurveyQuestionID && sqi.SurveyQuestionItemContent == theNewSurveyQuestionItem.SurveyQuestionItemContent) > 0;

					if (isExist)
					{
						msg = "选项已存在该问题中";
					}
					else
					{
						db.SurveyQuestionItems.InsertOnSubmit(theNewSurveyQuestionItem);
						db.SubmitChanges();

						result = db.SurveyQuestionItems.SingleOrDefault(sqi => sqi.SurveyQuestionID == theNewSurveyQuestionItem.SurveyQuestionID && sqi.SurveyQuestionItemContent == theNewSurveyQuestionItem.SurveyQuestionItemContent);
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteSurveyQuestionItem(int theSurveyQuestionItemID)
		{
			if (theSurveyQuestionItemID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbSurveyQuestionItem = db.SurveyQuestionItems.SingleOrDefault(sqi => sqi.SurveyQuestionItemID == theSurveyQuestionItemID);

					if (dbSurveyQuestionItem != null)
					{
						db.SurveyQuestionItems.DeleteOnSubmit(dbSurveyQuestionItem);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateSurveyQuestionItem(SurveyQuestionItem theSurveyQuestionItem)
		{
			if (theSurveyQuestionItem == null || theSurveyQuestionItem.SurveyQuestionItemID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbsurveyQuestionItem = db.SurveyQuestionItems.SingleOrDefault(sqi => sqi.SurveyQuestionItemID == theSurveyQuestionItem.SurveyQuestionItemID);

					if (dbsurveyQuestionItem != null)
					{
						dbsurveyQuestionItem.SurveyQuestionID = theSurveyQuestionItem.SurveyQuestionID;
						dbsurveyQuestionItem.SurveyQuestionItemContent = theSurveyQuestionItem.SurveyQuestionItemContent;
						dbsurveyQuestionItem.SurveyQuestionItemLabel = theSurveyQuestionItem.SurveyQuestionItemLabel;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public SurveyAnswer CreateSurveyAnswer(SurveyAnswer theNewSurveyAnswer, out string msg)
		{
			if (theNewSurveyAnswer == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			SurveyAnswer result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.SurveyAnswers.Count(sa => sa.SurveyAnswerUserID == theNewSurveyAnswer.SurveyAnswerUserID && sa.SurveyQuestionID == theNewSurveyAnswer.SurveyQuestionID && (sa.SurveyAnswerSelectedQuestionItemID == theNewSurveyAnswer.SurveyAnswerSelectedQuestionItemID || sa.SurveyAnswerContent == theNewSurveyAnswer.SurveyAnswerContent)) > 0;

					if (!isExist)
					{
						db.SurveyAnswers.InsertOnSubmit(theNewSurveyAnswer);
						db.SubmitChanges();

						result = db.SurveyAnswers.SingleOrDefault(sa => sa.SurveyQuestionID == theNewSurveyAnswer.SurveyQuestionID && sa.SurveyAnswerUserID == theNewSurveyAnswer.SurveyAnswerUserID && (sa.SurveyAnswerContent == theNewSurveyAnswer.SurveyAnswerContent || sa.SurveyAnswerSelectedQuestionItemID == theNewSurveyAnswer.SurveyAnswerSelectedQuestionItemID));
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<SurveyAnswer> GetSurveyAnswer(int userID, int questionID)
		{
			if (userID <= 0 || questionID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<SurveyAnswer> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from sa in db.SurveyAnswers
								  where sa.SurveyAnswerUserID == userID && sa.SurveyQuestionID == questionID
								  select sa;

					if (data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<SurveyAnswer> GetSurveyAnswerBySurvey(int surveyID)
		{
			if (surveyID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<SurveyAnswer> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					//DataLoadOptions dlo = new DataLoadOptions();
					//dlo.LoadWith<SurveyAnswer>(sa => sa.SurveyQuestionItem);
					//db.LoadOptions = dlo;

					var data = from sa in db.SurveyAnswers
								  where (from sq in db.SurveyQuestions
											where sq.SurveyID == surveyID
											select sq.SurveyQuestionID).Contains(sa.SurveyQuestionID)
								  select sa;

					if (data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<SurveyQuestionItem> GetSurveyQuestionItemByQuestion(int surveyQuestionID)
		{
			if (surveyQuestionID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<SurveyQuestionItem> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from sq in db.SurveyQuestionItems
								  where sq.SurveyQuestionID == surveyQuestionID
								  select sq;

					if (data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteSurveyQuestionItemByQuestion(int theSurveyQuestionID)
		{
			if (theSurveyQuestionID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var answers = db.SurveyAnswers.Where(sa => sa.SurveyQuestionID == theSurveyQuestionID);
					db.SurveyAnswers.DeleteAllOnSubmit(answers);

					var questionItems = db.SurveyQuestionItems.Where(sqi => sqi.SurveyQuestionID == theSurveyQuestionID);
					db.SurveyQuestionItems.DeleteAllOnSubmit(questionItems);

					db.SubmitChanges();

					result = true;
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public SurveyQuestion GetSurveyQuestion(int theSurveyQuestionID)
		{
			if (theSurveyQuestionID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			SurveyQuestion result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					result = db.SurveyQuestions.SingleOrDefault(sq => sq.SurveyQuestionID == theSurveyQuestionID);
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<SurveyQuestionItem> GetSurveyQuestionItemBySurvey(int surveyID)
		{
			if (surveyID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<SurveyQuestionItem> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from sqi in db.SurveyQuestionItems
								  where (from sq in db.SurveyQuestions
											where sq.SurveyID == surveyID
											select sq.SurveyQuestionID).Contains(sqi.SurveyQuestionID)
								  select sqi;

					if (data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool SaveSurveyAnswers(List<SurveyAnswer> lstSurveyAnswer)
		{
			if (lstSurveyAnswer == null || lstSurveyAnswer.Count <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					db.SurveyAnswers.InsertAllOnSubmit(lstSurveyAnswer);
					db.SubmitChanges();

					result = true;
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<RLT_SurveyAndShareTo> GetSurveyAndShareToBySurvey(int surveyID)
		{
			if (surveyID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<RLT_SurveyAndShareTo> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<RLT_SurveyAndShareTo>(ss => ss.User);
					db.LoadOptions = dlo;

					var data = from ss in db.RLT_SurveyAndShareTos
								  where ss.SurveyID == surveyID
								  select ss;

					if (data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public void CreateSurveyAndShareTo(List<RLT_SurveyAndShareTo> lstSurveyAndShareTo)
		{
			if (lstSurveyAndShareTo == null || lstSurveyAndShareTo.Count <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					db.RLT_SurveyAndShareTos.InsertAllOnSubmit(lstSurveyAndShareTo);
					db.SubmitChanges();
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}
		}

		public void SaveSurveyAndShareTo(int surveyID, List<RLT_SurveyAndShareTo> lstSurveyAndShareTo)
		{
			if (surveyID <= 0 || lstSurveyAndShareTo == null)
			{
				throw new ArgumentOutOfRangeException();
			}

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbSurveyAndShareTo = from vs in db.RLT_SurveyAndShareTos
													 where vs.SurveyID == surveyID
													 select vs;

					if (dbSurveyAndShareTo != null)
					{
						var lstDBSurveyAndShareTo = dbSurveyAndShareTo.ToList();

						var deleteItems = lstDBSurveyAndShareTo.Except(lstSurveyAndShareTo);

						if (deleteItems != null)
						{
							db.RLT_SurveyAndShareTos.DeleteAllOnSubmit(deleteItems);
						}

						var newItems = lstSurveyAndShareTo.Except(lstDBSurveyAndShareTo);

						if (newItems != null)
						{
							db.RLT_SurveyAndShareTos.InsertAllOnSubmit(newItems);
						}

						db.SubmitChanges();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}
		}

		public List<Survey> GetAllSurvey(int userID, int drugStoreID, int pageIndex, int pageSize, out int totalRecords)
		{
			if (drugStoreID <= 0 || userID <= 0)
			{
				throw new ArgumentException();
			}

			List<Survey> result = null;

			try
			{
				using (DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var user = db.Users.SingleOrDefault(u => u.UserID == userID);

					totalRecords = (from s in db.Surveys
										 where (s.DrugStoreID == user.DrugStoreID) || (s.IsPublic == 1) || ((from ss in db.RLT_SurveyAndShareTos
																																	where ss.ShareToDrugStoreID == user.DrugStoreID
																																	select ss.SurveyID).Contains(s.SurveyID)) || ((from ss in db.RLT_SurveyAndShareTos
																																																  where ss.ShareToMemberID == userID
																																																  select ss.SurveyID).Contains(s.SurveyID))
										 select s).Count();

					var data = (from s in db.Surveys
									where (s.DrugStoreID == user.DrugStoreID) || (s.IsPublic == 1) || ((from ss in db.RLT_SurveyAndShareTos
																															  where ss.ShareToDrugStoreID == user.DrugStoreID
																															  select ss.SurveyID).Contains(s.SurveyID)) || ((from ss in db.RLT_SurveyAndShareTos
																																															 where ss.ShareToMemberID == userID
																																															 select ss.SurveyID).Contains(s.SurveyID))
									orderby s.SurveyDate descending
									select s).Skip(pageIndex * pageSize).Take(pageSize);

					if (data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch (Exception exc)
			{
				throw exc;
			}

			return result;
		}

		#endregion
	}
}
